package dao.imp;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import dao.SupplierDAO;
import imageUtil.Page;
import model.Supplier;
import util.DBConnection;

public class SupplierDAOImp implements SupplierDAO {

	private static final String findByCount = "select count(*) from vendor";
	
	private static final String findByAll = "select * from vendor limit ?,?";
	
	private static final String insertSupplier = "insert into vendor(vendor,vendor_address,vendor_phone,vendor_fax,contact_person) values(?,?,?,?,?)";
	
	private static final String updateSupplier = "update vendor set vendor=?,vendor_address=?,vendor_phone=?,vendor_fax=?,contact_person=? where id=?";
	
	private static final String findByName = "select DISTINCT id,vendor from vendor";
	
	private static final String findNameById = "select vendor from vendor where id=?";
	@Override
	public int findByCount() throws Exception {
		PreparedStatement prep= null;
		Connection conn= null;
		int count= 0;
		ResultSet rst= null;
		try {
			conn= DBConnection.getConnection();		    	  
			prep= conn.prepareStatement(findByCount);
			rst= prep.executeQuery();
			if(rst.next()) {
				count=rst.getInt(1);
			}
		} finally{
			DBConnection.close(prep, conn);
		}
			return count; 
	}

	@Override
	public List<Supplier> findByAll(Page page) throws Exception {
		PreparedStatement prep= null;
		Connection conn= null;
		List<Supplier> reve = new ArrayList<Supplier>();
		Supplier i= null;
		ResultSet rst= null;
		try {
			conn= DBConnection.getConnection();		    	  
			prep= conn.prepareStatement(findByAll);
			prep.setInt(1, page.getBeginIndex());
			prep.setInt(2, page.getEveryPage());
			rst= prep.executeQuery();
			while(rst.next()) {
				i= new Supplier();
				i.setId(rst.getInt("id"));
				i.setVendor(rst.getString("vendor"));
				i.setVendorAddress(rst.getString("vendor_address"));
				i.setVendorPhone(rst.getString("vendor_phone"));
				i.setVendorFax(rst.getString("vendor_fax"));
				i.setContactPerson(rst.getString("contact_person"));
				reve.add(i);
			}
		} finally{
			DBConnection.close(prep, conn);
		}
			return reve; 
	}

	@Override
	public boolean saveSupplier(Supplier supplier) throws Exception {
		PreparedStatement prep = null;
		Connection conn = null;
		int i = 0;
		try{
			conn = DBConnection.getConnection();
			prep = conn.prepareStatement(insertSupplier);
			prep.setString(1, supplier.getVendor());
			prep.setString(2, supplier.getVendorAddress());
			prep.setString(3, supplier.getVendorPhone());
			prep.setString(4, supplier.getVendorFax());
			prep.setString(5, supplier.getContactPerson());
			i = prep.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				DBConnection.close(prep, conn);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		if(i != 0){
			return true;
		}else {
			return false;
		}
	}

	@Override
	public boolean updateSupplier(Supplier supplier) throws Exception {
		PreparedStatement prep = null;
		Connection conn = null;
		int i = 0;
		try{
			conn = DBConnection.getConnection();
			prep = conn.prepareStatement(updateSupplier);
			prep.setString(1, supplier.getVendor());
			prep.setString(2, supplier.getVendorAddress());
			prep.setString(3, supplier.getVendorPhone());
			prep.setString(4, supplier.getVendorFax());
			prep.setString(5, supplier.getContactPerson());
			prep.setInt(6, supplier.getId());
			i = prep.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				DBConnection.close(prep, conn);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		if(i != 0){
			return true;
		}else {
			return false;
		}	
	}

	@Override
	public List<Supplier> findByName() throws Exception {
		PreparedStatement prep= null;
		Connection conn= null;
		List<Supplier> reve = new ArrayList<Supplier>();
		ResultSet rst= null;
		try {
			conn = DBConnection.getConnection();
			prep = conn.prepareStatement(findByName);
			rst = prep.executeQuery();
			while(rst.next()){
				Supplier im = new Supplier();
				im.setId(rst.getInt("id"));
				im.setVendor(rst.getString("vendor"));
				reve.add(im);
			}
		} finally{
			DBConnection.close(prep, conn);
		}
		return reve;
	}

	@Override
	public String findNameById(int id) throws Exception {
		Connection conn = null;
		PreparedStatement prep = null; 
		ResultSet rs = null;
		try{
			conn = DBConnection.getConnection();
			prep = conn.prepareStatement(findNameById);
			prep.setInt(1, id);
			rs	= prep.executeQuery();
	        if(rs.next()) {	
				return rs.getString("vendor");
			}
		} finally {
			DBConnection.close(rs, prep, conn);
		}
		return null;
	}

}
